package com.core.family.gen.service.gen.Impl.mssql;

import com.core.family.gen.domain.po.TableBeanPo;
import com.core.family.gen.domain.po.TableColumnPo;
import com.core.family.gen.service.gen.Impl.AbsGenHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @description: mybatis 相关代码生成 通过链接器获取数据库元信息实现
 * @author: honglei
 * @time: 2021/3/23 0023 14:48
 */

public class GenMssqlHandler extends AbsGenHandler {

    //oracle 查询表名
    //   private final  static String QUEARY_TABLEAS = "select table_name as TABLE_NAME  tableName from user_tables ";
    private final static String QUEARY_TABLEAS =
            "select a.name AS TABLE_NAME, CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS REMARKS " +
                    " from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)";


    //查询表字段及注释
    private final static String QUEARY_COLUMNS =
            " SELECT cs.DATA_TYPE as TYPE_NAME,cs.COLUMN_NAME as COLUMN_NAME,CONVERT(NVARCHAR(100),isnull(ps.value,'-')) as REMARKS  " +
                    " FROM  sys.tables ta  " +
                    " INNER JOIN  information_schema.columns cs on ta.name=cs.TABLE_NAME " +
                    " LEFT JOIN sys.extended_properties ps ON ps.major_id = ta.object_id AND ps.minor_id = cs.ORDINAL_POSITION " +
                    " WHERE ta.name = ? ";


    @Override
    public List<TableBeanPo> tablesList() throws Exception {
        List<TableBeanPo> list = new ArrayList<>();

        if (list.size() == 0) {
            Connection connection = getConnection();
            ResultSet rs = connection.createStatement().executeQuery(QUEARY_TABLEAS);
            list = filledTableBean(rs);
            connection.clearWarnings();
        }
        return list;
    }


    @Override
    public List<TableColumnPo> getColumns(String table) throws Exception {

        List<TableColumnPo> list = new ArrayList<>();

        if (list.size() == 0) {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(QUEARY_COLUMNS);
            ps.setString(1, table);
            ResultSet resultSet = ps.executeQuery();
            list = filledTableColumn(resultSet);
            connection.close();
        }
        return list;
    }
}
